import pandas as pd
import glob
import re
import warnings
from openpyxl.styles.stylesheet import Stylesheet

# 添加警告过滤
warnings.filterwarnings('ignore', category=UserWarning, module='openpyxl.styles.stylesheet')

def parse_filename(filename):
    """从文件名提取年级、考试标识和班级ID"""
    match = re.search(r'(高一|高二|高三).*?(第\w+周周考).*?（(\d+)）班', filename)
    if not match:
        raise ValueError("文件名格式不正确")
    return match.group(1), match.group(2), f"{match.group(3)}班"

def process_file(file_path):
    """处理单个成绩单文件"""
    # 严格以字符串形式读取学号列
    df = pd.read_excel(file_path, skiprows=2, header=None, dtype={1: str})
    result = pd.DataFrame()
    # 确保学号列以 4 位字符串形式处理
    result['student_id'] = df[1].apply(lambda x: str(x).zfill(4))
    result['name'] = df[0]
    result['biology_score'] = df[2]
    rank_split = df[3].str.split('/', expand=True)
    result['class_rank'] = rank_split[0].astype(int)
    result['school_rank'] = rank_split[1].astype(int)
    return result

def main():
    all_data = []
    for file in glob.glob('*.xlsx'):
        if '成绩单' not in file:
            continue
        try:
            grade, exam_id, class_id = parse_filename(file)
            df = process_file(file)
            df['grade'] = grade
            df['exam_id'] = exam_id
            df['class_id'] = class_id
            all_data.append(df)
        except Exception as e:
            print(f"处理文件 {file} 失败: {str(e)}")
            continue
    if not all_data:
        print("未找到符合条件的文件")
        return
    final_df = pd.concat(all_data)
    final_df = final_df[[
        'grade',
        'exam_id',
        'class_id',
        'student_id',
        'name',
        'biology_score',
        'school_rank',
        'class_rank'
    ]]
    output_filename = f"{all_data[0]['exam_id'].iloc[0]}.xlsx"
    # 使用 openpyxl 引擎保存
    with pd.ExcelWriter(output_filename, engine='openpyxl') as writer:
        final_df.to_excel(writer, index=False)
        workbook = writer.book
        worksheet = writer.sheets['Sheet1']
        # 获取学号列的索引
        student_id_col_index = final_df.columns.get_loc('student_id') + 1
        # 设置学号列的单元格格式为文本
        for row in range(2, len(final_df) + 2):
            cell = worksheet.cell(row=row, column=student_id_col_index)
            cell.number_format = '@'
    print(f"合并完成，共处理 {len(all_data)} 个文件，保存为: {output_filename}")

if __name__ == "__main__":
    main()